/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Persistencia;

import Negocio.Bicicleta;
import Negocio.Pieza;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

/**
 *
 * @author JAVA
 */
public class PPieza {

    public static void crearPiezaBD(Pieza p, int id_bicicleta) {

        Connection connection = null;
        Statement statement = null;


        try {
            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection(PDatos.baseDatos, PDatos.usuario, PDatos.password);
            statement = connection.createStatement();

            statement.executeUpdate("INSERT INTO pieza (nombre, modelo, id_bici) values ('"
                    + p.getNombre() + "', '" + p.getModelo() + "', "
                    + id_bicicleta + ")");

        } catch (Exception ex) {
            System.out.println(ex.toString());
        } finally {
            try {

                statement.close();
                connection.close();
            } catch (Exception ex) {
                System.out.println(ex.toString());
            }
        }
    }

    public static void actualizarPiezaBD(Pieza p) {

        Connection connection = null;
        Statement statement = null;


        try {
            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection(PDatos.baseDatos, PDatos.usuario, PDatos.password);
            statement = connection.createStatement();

            statement.executeUpdate("UPDATE pieza "
                    + "SET NOMBRE = '" + p.getNombre()
                    + "' ,MODELO = '" + p.getModelo()
                    + "' where id = " + p.getId());

        } catch (Exception ex) {
            System.out.println(ex.toString());
        } finally {
            try {

                statement.close();
                connection.close();
            } catch (Exception ex) {
                System.out.println(ex.toString());
            }
        }
    }

    public static void eliminarPiezaBD(Pieza p) {

        Connection connection = null;
        Statement statement = null;

        try {
            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection(PDatos.baseDatos, PDatos.usuario, PDatos.password);
            statement = connection.createStatement();

            statement.executeUpdate("DELETE from pieza WHERE id = " + p.getId());

        } catch (Exception ex) {
            System.out.println(ex.toString());
        } finally {
            try {
                statement.close();
                connection.close();
            } catch (Exception ex) {
                System.out.println(ex.toString());
            }
        }
    }

    public static Pieza seleccionarPiezaBD(int id_pieza) {

        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        Pieza p = null;

        try {
            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection(PDatos.baseDatos, PDatos.usuario, PDatos.password);
            statement = connection.createStatement();

            String SQL = "SELECT id, nombre, modelo from pieza where id = " + id_pieza;
            
            resultSet = statement.executeQuery(SQL);

            while (resultSet.next()) {
                p = new Pieza (resultSet.getString("nombre"), resultSet.getString("modelo"), resultSet.getInt("id"));               
            }
        } catch (Exception ex) {
            System.out.println(ex.toString());
        } finally {
            try {
                resultSet.close();
                statement.close();
                connection.close();
            } catch (Exception ex) {
                System.out.println(ex.toString());
            }
        }
        return p;
    }
    
    
    public static List<Pieza> devolverListaPiezaBD(Bicicleta b) {

        List<Pieza> listPiezas = new ArrayList();

        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;

        try {
            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection(PDatos.baseDatos, PDatos.usuario, PDatos.password);
            statement = connection.createStatement();

            String SQL = "SELECT id from pieza where id_bici = " + b.getId();
            
            resultSet = statement.executeQuery(SQL);

            while (resultSet.next()) {
                Pieza p = seleccionarPiezaBD(resultSet.getInt("id"));
                listPiezas.add(p);               
            }
        } catch (Exception ex) {
            System.out.println(ex.toString());
        } finally {
            try {
                resultSet.close();
                statement.close();
                connection.close();
            } catch (Exception ex) {
                System.out.println(ex.toString());
            }
        }
        return listPiezas;
    }
}
